﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using XLETL.IDAL;
using XLETL.Model;
using XLETL.ExceptionHandling;

namespace XLETL.SQLServerDAL
{
    public class TargetDatabase : ITargetDatabase
    {
        // TODO: fix query when index is defined the column record will be twice
        const string SELECT_TBL_COLUMNS = "Select Distinct PKColumn.PK_Column, PFKs.FK_Column, PFKs.PK_Table, PFKs.Constraint_Name, o.object_id, c.column_id, " +
"o.name as TableName, c.name as ColumnName, t.name as TypeName, TypeLength = case when t.name='nvarchar' then c.max_length/2 else c.max_length end, " +
"c.precision as TypePrecision, c.scale as TypeScale, c.is_nullable as IsNullable, c.is_rowguidcol as IsRowGuid, c.is_identity as IsIdentity from sys.objects as o " +
"inner join sys.columns as c on o.object_id = c.object_id inner join sys.types as t on t.system_type_id = c.system_type_id and t.user_type_id = c.user_type_id  " +
"left outer join ( SELECT FK_Table  = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table  = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " +
    "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME " +
    "INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT " +
    "ON PT.TABLE_NAME = PK.TABLE_NAME Where FK.TABLE_NAME = '{0}') PFKs ON PFKs.FK_Column = c.name left outer join ( select c.COLUMN_NAME AS PK_Column " +
    "from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '{0}' and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME ) PKColumn ON c.name = PKColumn.PK_Column where o.name = '{0}' order by c.column_id";

        /// <summary>
        /// Get table list for particular database specified in the connection string
        /// </summary>
        /// <param name="connectionString">Connection string to the target database</param>
        public DataTable GetDatabaseTableList(string connectionString)
        {
            DataTable dtResult = new DataTable();
            try
            {
                dtResult = GetDBSchemaTable("System.Data.SqlClient", connectionString, "Tables", null);
                dtResult.DefaultView.Sort = "TABLE_NAME";
            }
            catch (SqlException sqlEx)
            {
                //ExceptionProcessor.ProcessSQLDALException(sqlEx);
              throw new DataException("Error in DAL", sqlEx);
            }
            catch (DbException dbEx)
            {
                //ExceptionProcessor.ProcessDALException(dbEx.InnerException);
              throw new DataException("Error in DAL", dbEx);
            }
            catch (Exception ex)
            {
                //ExceptionProcessor.ProcessDALException(ex);
              throw new Exception("Error in DAL", ex);
            }
            return dtResult;
        }


        private DataTable GetDBSchemaTable(string providerName, string cnSettings, string collectionName, string[] restrictions)
        {
            DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);

            using (DbConnection cn = provider.CreateConnection())
            {
                cn.ConnectionString = cnSettings;
              
                cn.Open();
                if (collectionName == null)
                {
                    return cn.GetSchema();
                }
                return cn.GetSchema(collectionName, restrictions);
            }
        }

        public DataTable GetColumnListByTable(string tableName, string connString, string ColumnCommaDelimitedString)
        {
            DataTable dtResult = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(connString))
                {
                    con.Open();

                    // Create and configure a new command.
                    using (SqlCommand com = con.CreateCommand())
                    {
                        com.CommandType = CommandType.Text;
                        //com.CommandText = string.Format("select o.object_id, c.column_id, ic.index_id, o.name as TableName, c.name as ColumnName, t.name as TypeName, c.max_length as TypeLength, c.precision as TypePrecision, c.scale as TypeScale, c.is_nullable as IsNullable, c.is_rowguidcol as IsRowGuid, c.is_identity as IsIdentity from sys.objects as o inner join sys.columns as c on o.object_id = c.object_id inner join sys.types as t on t.system_type_id = c.system_type_id and t.user_type_id = c.user_type_id left outer join sys.index_columns as ic on c.column_id = ic.column_id and o.object_id = ic.object_id where o.name = '{0}' {1}", tableName, (ColumnCommaDelimitedString == null ? "" : string.Format("and c.name in ({0})", ColumnCommaDelimitedString)));
                        com.CommandText = string.Format(SELECT_TBL_COLUMNS, tableName);
                        dtResult.Load(com.ExecuteReader(CommandBehavior.CloseConnection));
                    }
                }
            }
            catch (Exception ex)
            {
                //ExceptionProcessor.ProcessDALException(ex);
              throw new Exception("Error in DAL", ex);
            }

            return dtResult;

        }

        public List<TargetColumnInfo> GetTargetColumnListByTableName(string tableName, string connString, string ColumnCommaDelimitedString)
        {
            List<TargetColumnInfo> trgColumnList = new List<TargetColumnInfo>();
            TargetColumnInfo obj = null;
            try
            {
                using (SqlConnection con = new SqlConnection(connString))
                {
                    con.Open();

                    // Create and configure a new command.
                    using (SqlCommand com = con.CreateCommand())
                    {
                        com.CommandType = CommandType.Text;
                        //com.CommandText = string.Format("select o.object_id, c.column_id, ic.index_id, o.name as TableName, c.name as ColumnName, t.name as TypeName, c.max_length as TypeLength, c.precision as TypePrecision, c.scale as TypeScale, c.is_nullable as IsNullable, c.is_rowguidcol as IsRowGuid, c.is_identity as IsIdentity from sys.objects as o inner join sys.columns as c on o.object_id = c.object_id inner join sys.types as t on t.system_type_id = c.system_type_id and t.user_type_id = c.user_type_id left outer join sys.index_columns as ic on c.column_id = ic.column_id and o.object_id = ic.object_id where o.name = '{0}' {1}", tableName, (ColumnCommaDelimitedString == null ? "" : string.Format("and c.name in ({0})", ColumnCommaDelimitedString)));
                        com.CommandText = string.Format(SELECT_TBL_COLUMNS, tableName);
                        using (SqlDataReader reader = com.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                obj = new TargetColumnInfo();
                                obj.ID = Guid.NewGuid();
                                obj.Name = reader.GetString(reader.GetOrdinal("ColumnName"));
                                obj.Size = reader.GetInt32(reader.GetOrdinal("TypeLength"));
                                obj.Type = reader.GetString(reader.GetOrdinal("TypeName"));
                                obj.IsDefaultNull = reader.GetBoolean(reader.GetOrdinal("IsNullable"));
                                obj.IsIdentity = reader.GetBoolean(reader.GetOrdinal("IsIdentity"));

                                obj.IsPrimaryKey = !reader.IsDBNull(reader.GetOrdinal("PK_Column"));
                                obj.IsForeignKey = !reader.IsDBNull(reader.GetOrdinal("FK_Column"));
                                obj.OrderIndex = reader.GetInt32(reader.GetOrdinal("column_id"));

                                if (!reader.IsDBNull(reader.GetOrdinal("PK_Table")))
                                {
                                    obj.PrimaryToForeignTableName = reader.GetString(reader.GetOrdinal("PK_Table"));                                    
                                }

                                if (!reader.IsDBNull(reader.GetOrdinal("Constraint_Name")))                                
                                {                                    
                                    obj.ConstraintName = reader.GetString(reader.GetOrdinal("Constraint_Name"));
                                }

                                trgColumnList.Add(obj);
                            }                            
                        }                       
                    }
                }
            }
            catch (Exception ex)
            {
                //ExceptionProcessor.ProcessDALException(ex);
              throw new Exception("Error in DAL", ex);
            }

            return trgColumnList;

        }

        public DataTable GetDataOfTable(string connString, string tableName, string[] columns)
        {
            string selectQuery = "Select {0} from [{1}]";
            StringBuilder columnsString = new StringBuilder();

            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = connString;
                // Create and configure a new command.

                using (SqlCommand com = con.CreateCommand())
                {
                    com.CommandType = CommandType.Text;

                    if (columns != null && columns.Length > 0)
                    {
                        for (int i = 0; i < columns.Length; i++)
                        {
                            columnsString.AppendFormat("[{0}],", columns[i]);
                        }
                        columnsString.Remove(columnsString.Length - 1, 1); // remove last comma
                    }
                    else // no column names provided then we will retrieve all
                    {
                        columnsString.Append("*");
                    }

                    com.CommandText = string.Format(selectQuery, columnsString.ToString(), tableName);
                    con.Open();
                   
                    SqlDataAdapter da = new SqlDataAdapter(com);

                    DataTable _dataTable = new DataTable();
                    _dataTable.TableName = tableName;

                    da.Fill(_dataTable);                   

                    da.Dispose();
                    con.Close();

                    return _dataTable;
                }
            }
        }
    }
}
